package com.zlj.jdbc.dbutils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import com.zlj.jdbc.jdbcUtils02.JdbcUtils;

/**
 * 1、使用DBUtils完成添加和查询操作；
 * 2、DBUtils结果集处理器介绍
 * 
 * @author bashen
 *
 */
public class DbUtilsDemo {
	/**
	 * 使用DBUtils完成增加操作
	 * @throws SQLException
	 */
	@Test
	public void addEmployee() throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "insert into employee values(?,?,?,?)";
		Object[] params = {4,"tom","tom@163.com","female"};
		runner.update(sql, params);
	}
	
	/**
	 * 使用DBUtils完成查询操作
	 * @throws SQLException 
	 */
	@Test
	public void queryEmployee() throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select * from employee where id = ?";
		Object[] params = {1};
		ResultSetHandler<Employee> handler = new ResultSetHandler<Employee>() {
			@Override
			public Employee handle(ResultSet resultSet) throws SQLException {
                Employee employee = new Employee();
                if(!resultSet.next()) {
                	return null;
                }
                employee.setId(resultSet.getInt("id"));
                employee.setName(resultSet.getString("name"));
                employee.setEmail(resultSet.getString("email"));
                employee.setGender(resultSet.getString("gender"));
				return employee;
			}
		};
		/**
		 * 执行query方法，需要给出结果集处理器，即ResultSetHandler的实现类对象
		 * 其中BeanHandler就是ResultSetHandler的一个实现类对象
		 * BeanHandler需要一个类型，然后它会把resultSet中的数据封装到指定类型的JavaBean对象中，然后返回JavaBean对象。
		 * 
		 * 需要注意的是，其中数据库中的字段名要和JavaBean对象中的属性名一致！
		 */
		Employee employee = runner.query(sql, new BeanHandler<Employee>(Employee.class), params);
		//Employee [id=1, name=tom, email=tom@163.com, gender=female]
		System.out.println(employee);
	}
	
	/*
	 * DBUtils结果集处理器介绍
	 */
	
	/**
	 * BeanListHandler的应用：它是多行结果集处理器，每行对应一个Employee对象。
	 * 
	 * @throws SQLException
	 */
	@Test
	public void test01() throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select * from employee";
		List<Employee> employeeList = qr.query(sql, new BeanListHandler<Employee>(Employee.class));
		//[Employee [id=1, name=tom, email=tom@163.com, gender=female], Employee [id=2, name=jack, email=jack@163.com, gender=m], Employee [id=3, name=zlj, email=zlj@163.com, gender=male], Employee [id=4, name=tom, email=tom@163.com, gender=female], Employee [id=7, name=张三, email=zlj@123.com, gender=female], Employee [id=8, name=水电费, email=对方答复 , gender=male]]
		System.out.println(employeeList);
	}
	
	/**
	 * MapHandler的应用：它是单行处理器，把一行转换为一个Map对象。
	 * @throws SQLException 
	 */
	@Test
	public void test02() throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select * from employee where id = ?";
		Object[] params = {1};
        Map<String, Object> employeeMap = qr.query(sql, new MapHandler(), params);
        //{id=1, name=tom, email=tom@163.com, gender=female}
		System.out.println(employeeMap);
	}
	
	/**
	 * MapListHandler的应用：它是多行结果集处理器，把每一行转换为一个Map对象，即 List<Map>
	 * @throws SQLException 
	 */
	@Test
	public void test03() throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select * from employee";
        List<Map<String, Object>> employeeMapList = qr.query(sql, new MapListHandler());
        //[{id=1, name=tom, email=tom@163.com, gender=female}, {id=2, name=jack, email=jack@163.com, gender=m}, {id=3, name=zlj, email=zlj@163.com, gender=male}, {id=4, name=tom, email=tom@163.com, gender=female}, {id=7, name=张三, email=zlj@123.com, gender=female}, {id=8, name=水电费, email=对方答复 , gender=male}]
		System.out.println(employeeMapList);
	}
	
	/**
	 * ScalarHandler的应用：它是单行单列时使用最为合适！
	 * @throws SQLException 
	 */
	@Test
	public void test04() throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select count(*) from employee";
		/**
		 * 驱动包不同，返回的类型不同
		 * 会返回：Integer、Long、BigInteger这几种类型
		 * 这几种类型都有一个共同的父类，就是Number，所以采用Number接收，最后转换为自己想要的类型，通过intValue()或者longValue()等
		 */
		Number employeeScalar = qr.query(sql, new ScalarHandler<>());
		Long c = employeeScalar.longValue();
		System.out.println(c);  //6
	}
}
